const { DataTypes } = require('sequelize');
const { sequelize } = require('../config/database');

const Lecture = sequelize.define('Lecture', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true,
    comment: '讲义ID'
  },
  chapter_id: {
    type: DataTypes.STRING(20),
    allowNull: false,
    comment: '所属章节ID'
  },
  name: {
    type: DataTypes.STRING(200),
    allowNull: false,
    comment: '文件名称'
  },
  title: {
    type: DataTypes.STRING(200),
    allowNull: false,
    comment: '讲义标题'
  },
  path: {
    type: DataTypes.STRING(500),
    allowNull: false,
    comment: '文件路径'
  },
  file_type: {
    type: DataTypes.ENUM('pdf', 'md', 'doc', 'docx', 'ppt', 'pptx', 'other'),
    allowNull: false,
    defaultValue: 'other',
    comment: '文件类型'
  },
  file_size: {
    type: DataTypes.BIGINT,
    defaultValue: 0,
    comment: '文件大小（字节）'
  },
  sort_order: {
    type: DataTypes.INTEGER,
    allowNull: false,
    defaultValue: 0,
    comment: '在章节内的排序顺序'
  },
  status: {
    type: DataTypes.TINYINT(1),
    allowNull: false,
    defaultValue: 1,
    comment: '状态：1-启用，0-禁用'
  }
}, {
  tableName: 'lectures',
  timestamps: true,
  createdAt: 'created_at',
  updatedAt: 'updated_at',
  charset: 'utf8mb4',
  collate: 'utf8mb4_unicode_ci',
  comment: '讲义表',
  indexes: [
    {
      fields: ['chapter_id']
    },
    {
      fields: ['status']
    },
    {
      fields: ['sort_order']
    }
  ]
});

// 定义关联关系
Lecture.associate = function(models) {
  Lecture.belongsTo(models.Chapter, {
    foreignKey: 'chapter_id',
    as: 'chapter',
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE'
  });
};

// 静态方法
Lecture.getAll = async function() {
  return await Lecture.findAll({
    where: { status: 1 },
    include: [{
      association: 'chapter'
    }],
    order: [['chapter_id', 'ASC'], ['sort_order', 'ASC']]
  });
};

Lecture.getByChapterId = async function(chapterId) {
  return await Lecture.findAll({
    where: { 
      chapter_id: chapterId,
      status: 1 
    },
    order: [['sort_order', 'ASC']]
  });
};

Lecture.getById = async function(id) {
  return await Lecture.findOne({
    where: { id, status: 1 },
    include: [{
      association: 'chapter'
    }]
  });
};

Lecture.createLecture = async function(data) {
  // 自动检测文件类型
  if (!data.file_type && data.name) {
    const extension = data.name.split('.').pop().toLowerCase();
    const typeMap = {
      'pdf': 'pdf',
      'md': 'md',
      'doc': 'doc',
      'docx': 'docx',
      'ppt': 'ppt',
      'pptx': 'pptx'
    };
    data.file_type = typeMap[extension] || 'other';
  }

  return await Lecture.create(data);
};

Lecture.updateLecture = async function(id, data) {
  const [updatedRowsCount] = await Lecture.update(data, {
    where: { id }
  });
  return updatedRowsCount;
};

Lecture.deleteLecture = async function(id) {
  const [updatedRowsCount] = await Lecture.update(
    { status: 0 },
    { where: { id } }
  );
  return updatedRowsCount;
};

Lecture.updateSortOrder = async function(lectureIds) {
  const promises = lectureIds.map((id, index) => 
    Lecture.update(
      { sort_order: index + 1 },
      { where: { id } }
    )
  );
  
  return await Promise.all(promises);
};

Lecture.getByFileType = async function(fileType) {
  const Chapter = require('./Chapter');
  return await Lecture.findAll({
    where: { 
      file_type: fileType,
      status: 1 
    },
    include: [{
      model: Chapter,
      as: 'chapter'
    }],
    order: [['chapter_id', 'ASC'], ['sort_order', 'ASC']]
  });
};

Lecture.search = async function(keyword) {
  const Chapter = require('./Chapter');
  const { Op } = require('sequelize');
  
  return await Lecture.findAll({
    where: {
      status: 1,
      [Op.or]: [
        { title: { [Op.like]: `%${keyword}%` } },
        { name: { [Op.like]: `%${keyword}%` } }
      ]
    },
    include: [{
      model: Chapter,
      as: 'chapter'
    }],
    order: [['chapter_id', 'ASC'], ['sort_order', 'ASC']]
  });
};

Lecture.getStats = async function() {
  const result = await Lecture.findOne({
    attributes: [
      [sequelize.fn('COUNT', sequelize.col('*')), 'total_lectures'],
      [sequelize.fn('COUNT', sequelize.literal('CASE WHEN status = 1 THEN 1 END')), 'active_lectures'],
      [sequelize.fn('COUNT', sequelize.fn('DISTINCT', sequelize.col('chapter_id'))), 'chapters_with_lectures']
    ],
    raw: true
  });
  
  const typeStats = await Lecture.findAll({
    attributes: [
      'file_type',
      [sequelize.fn('COUNT', sequelize.col('*')), 'count']
    ],
    where: { status: 1 },
    group: ['file_type'],
    raw: true
  });
  
  return {
    ...result,
    by_type: typeStats
  };
};

module.exports = Lecture;